﻿

-- =============================================
-- Author:		<赵洪涛>
-- Create date: <2012-06-05>
-- Description:	<WebService用户登录验证>
-- =============================================
CREATE PROCEDURE [dbo].[proc_WebService_GetCustomerInfo] 
(
	@UserId varchar(10),
	@StartDate nvarchar(10),
	@EndDate nvarchar(10)
)
AS
BEGIN
	Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime,@StartDate+' 00:00:00')
	Set @eRq = Convert(Datetime,@EndDate+' 23:59:59')

	SELECT Top 500 [CustomerName]
		,IsNull((Select Top 1 EName From Employee E Where E.UserName=@UserId),'未知') As EName
		,CreateDate
		,CustomerId
		,(Select Count(0) From [Order] O Where O.CusId=C.CustomerId And O.CreateDate>=@sRq) As OrderCount
		,IsNull((Select Sum(YingShouPrice) From [Order] O Where O.CusId=C.CustomerId And O.CreateDate>=@sRq),0) As YingShouPrice
		,IsNull((Select Sum(ShiShouPrice) From [Order] O Where O.CusId=C.CustomerId And O.CreateDate>=@sRq),0) As ShiShouPrice
	FROM Customer C
	Where CompanyId=1 And Audit=1 And CreateName=@UserId And CreateDate>=@sRq And CreateDate<=@eRq
	Order By OrderCount Desc,CreateDate Desc,Id Desc
END

